/*
 * @Author: your name
 * @Date: 2020-05-06 10:57:03
 * @LastEditTime: 2020-08-07 10:46:18
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: \truck\platform\sqlite\xeSqlite.c
 */
#include "xeSqlite.h"
#include <sys/time.h>
#include <time.h>
#include <linux/rtc.h>
#include "pthread.h"
#include "src_mgnt.h"
#include "av_play.h"
#include "query_info.h"

#include "userMgntApi.h"
#include "region_mgnt.h"

sqlite3* g_userMgntDb=NULL;
sqlite3 *g_sysMgntDB = NULL;
sqlite3 * g_urlMapDB=NULL;
sqlite3 *g_regionMgntDB = NULL;

pthread_mutex_t  transaction_syn_mutx=PTHREAD_MUTEX_INITIALIZER;
pthread_mutex_t  ipcdb_mutex=PTHREAD_MUTEX_INITIALIZER;
bool HISOME_urlCorrect=false;

// sqlite3 *ipc_db = NULL;
const char * CRTE_UrlMapTbl="CREATE TABLE IF NOT EXISTS %s ("\
                "h_url    TEXT UNIQUE DEFAULT [/hisome/test], "\
                "m_get    BIGINT DEFAULT (0),"\
                "m_post   BIGINT DEFAULT (0),"\
                "m_delete BIGINT DEFAULT (0),"\
                "m_query  BIGINT DEFAULT (0),"\
                "remarks  TEXT)";


/*************** start sysmgnt ver  ********************/
const char *sql_create_ver_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " ver         INTEGER   DEFAULT (0))"; 

/*************** start sysmgnt ipcmgnt ********************/
const char *sql_create_ipcmgnt_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " collector_ip      	CHAR (32)   DEFAULT NULL," \
                                        " collector_mac   	CHAR (32)   DEFAULT NULL," \
                                        " collector_sn      CHAR (64)   DEFAULT NULL," \
                                        " collector_name      CHAR (256)   DEFAULT NULL," \
                                        " ipc_sn        		CHAR (64)   DEFAULT [0] UNIQUE," \
                                        " ipc_mac     		CHAR (64)   DEFAULT [0]," \
                                        " ipc_name     		CHAR (128)   DEFAULT [0]," \
                                        " start_time        BIGINT     DEFAULT (0)," \
                                        " end_time     	    BIGINT     DEFAULT (0)," \
                                        " harddisk_sn      	CHAR (128)   DEFAULT [0]," \
                                        " harddisk_dir        CHAR (128)   DEFAULT [0]," \
                                        " storage_path         CHAR (128)   DEFAULT [0]," \
                                        " storage_db 	 	CHAR (128)   DEFAULT [ipc.db])"; 

/*************** start sysmgnt space ********************/
const char *sql_create_spacemgnt_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " harddisk_sn      	  CHAR (128)   DEFAULT [0]," \
                                        " harddisk_dir        CHAR (128)   DEFAULT [0]," \
                                        " dir_limit         INTEGER   DEFAULT (0)," \
                                        " dir_used          INTEGER   DEFAULT (0)," \
                                        " dir_free   	    INTEGER   DEFAULT (0))";

/*************** end sysmgnt ********************/

const char *sql_create_file_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " file_name      	CHAR (256)   DEFAULT [0]," \
                                        " detail_info_name  CHAR (256)   DEFAULT [0]," \
                                        " start_time        BIGINT     DEFAULT (0)," \
                                        " end_time     	    BIGINT     DEFAULT (0)," \
                                        " duration      	INTEGER     DEFAULT (0)," \
                                        " file_size       	BIGINT     DEFAULT (0)," \
                                        " file_space    	BIGINT     DEFAULT (0))"; 

const char *sql_create_statistics_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " num_files      INTEGER     DEFAULT (0)," \
                                        " start_time     BIGINT     DEFAULT (0)," \
                                        " end_time     	 BIGINT     DEFAULT (0)," \
                                        " total_size     BIGINT     DEFAULT (0)," \
                                        " total_space    BIGINT     DEFAULT (0)," \
                                        " space_limit    BIGINT     DEFAULT (0))"; 

const char *sql_create_region_tree_table = "CREATE TABLE %s" \
                                        "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
                                        " indexcode      CHAR (64)   DEFAULT [0]," \
                                        " region_name     CHAR (256)   DEFAULT [0]," \
                                        " parent_indexcode     	 CHAR (64)   DEFAULT [0]," \
                                        " collector_gts     CHAR (64)   DEFAULT [0])"; 

sqlite3 *tiny_sqlite3_open(const char * db_name)
{
	int rc;
	sqlite3 *in_db;
	rc = sqlite3_open(db_name, &in_db);
	if(rc){
		xeLOG_NOTICE("Can't open database:%s,err: %s",db_name, sqlite3_errmsg(in_db));
        xeLOG_ERR("try to reboot");
        sync();
        sleep(2);
        exit(0);
		return NULL;
	}
	return in_db;
}
void tiny_sqlite3_close(sqlite3 *pDb_in)
{
	if(!pDb_in)
		sqlite3_close(pDb_in);
}



/*memary db operate*/
int  db_openDB()
{
    int ret=MYSQLITE3_OK;
    int rc=MYSQLITE3_OK;
    rc = sqlite3_open(MEMORY_DB, &g_urlMapDB);
    if(rc){
        PRTLOG_ERR("Can't open mem  database: url2map reason:%s", sqlite3_errmsg(g_urlMapDB));
    }
    ret += rc;
    return ret;
}

void db_closeDb(sqlite3 * db)
{
    if(db != NULL)sqlite3_close(db);
}
/*db transaction operate*/
int  db_transBegin(sqlite3 *db)
{
    int rc=0; 
    char *errMsg;
    if(db==NULL){
        PRTLOG_ERR("db is NULL");
        return -1;
    }
    rc = sqlite3_exec(db,"begin immediate transaction", NULL, 0, &errMsg);
    if( rc != SQLITE_OK ){
      PRTLOG_ERR("SQL error: %s\n", errMsg);
      sqlite3_free(errMsg);
    }
    return rc;
}
int  db_transCommit(sqlite3 *db)
{
    int rc=0; 
    char *errMsg;
    if(db==NULL){
      PRTLOG_ERR("db is NULL");
        return -1;
    }
    rc = sqlite3_exec(db,"commit transaction", NULL, 0, &errMsg);
    if( rc != SQLITE_OK ){
      PRTLOG_ERR("SQL error: %s", errMsg);
      sqlite3_free(errMsg);
    }
    return rc;
}
int  db_transEnd(sqlite3 *db)
{
    int rc=0; 
    char *errMsg;
    if(db==NULL){
      PRTLOG_ERR("db is NULL");
        return -1;
    }
    rc = sqlite3_exec(db,"end transaction", NULL, 0, &errMsg);
    if( rc != SQLITE_OK ){
      PRTLOG_ERR("SQL error: %s\n", errMsg);
      sqlite3_free(errMsg);
    }
    return rc;
}
int  db_transRollback(sqlite3 *db)
{
    int rc=0; 
    char *errMsg;
    if(db==NULL){
      PRTLOG_ERR("db is NULL");
        return -1;
    }
    rc = sqlite3_exec(db,"rollback", NULL, 0, &errMsg);
    if( rc != SQLITE_OK ){
      PRTLOG_ERR("SQL error: %s\n",errMsg);
      sqlite3_free(errMsg); 
    }
    return rc;
}

/*url map db operate*/
static int  db_insertUrlMap(sqlite3 *db,UrlMap *urlmap){
     long m_get,m_post,m_delete,m_query;
     char remarks[1024];
     if(urlmap->h_url==NULL){
         PRTLOG_ERR("url is null");
         return SQLITE_FAIL;
     }
     m_get=(urlmap->m_get!=NULL)?(long)(urlmap->m_get):0;
     m_post=(urlmap->m_post!=NULL)?(long)(urlmap->m_post):0;
     m_delete=(urlmap->m_delete!=NULL)?(long)(urlmap->m_delete):0;
     m_query=(urlmap->m_query!=NULL)?(long)(urlmap->m_query):0;
     if(urlmap->remarks != NULL){
         strcpy(remarks,urlmap->remarks);
     }else{
         strcpy(remarks," ");
     }
     char sqlcmd[512]={'\0'};
     sprintf(sqlcmd,"INSERT INTO %s values(\"%s\",%ld,%ld,%ld,%ld,\"%s\")",URLMAP_TBL,urlmap->h_url,m_get,m_post,m_delete,m_query,remarks);
     return db_generalExec(db,sqlcmd);
}

int  db_fillUrlMapDB(){
      if(SQLITE_OK != db_createGeneralTbl(g_urlMapDB,CRTE_UrlMapTbl,URLMAP_TBL)){
            PRTLOG_ERR("Create table %s in database %s Fail",URLMAP_TBL,URLMAP_DB);
            return MYSQLITE3_CREATE_TABLE_FAIL;
      }else{
            ///download file from video collector
            if(SQLITE_OK != db_insertInterfae(AV_UPLOAD_URL,NULL,src_av_upload_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_DOWNLOAD,NULL,dowdload_play_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_PLAY,NULL,dowdload_play_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;

            if(SQLITE_OK != db_insertInterfae(AV_PLAY_SPEED_CTL,NULL,av_ctl_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_PLAY_PAUSE,NULL,av_ctl_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_PLAY_RESUME,NULL,av_ctl_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_PLAY_STOP,NULL,av_ctl_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_DL_RATE,NULL,av_ctl_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;

            if(SQLITE_OK != db_insertInterfae(AV_QUERY_IPCLIST,NULL,NULL,NULL,query_info_setup))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_QUERY_FLIELIST,NULL,NULL,NULL,query_info_setup))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(AV_DELETE_DEVICE,NULL,NULL,delete_info_setup,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(TINY_LOGIN,NULL,apiUsrMgnt_POST_Login,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(TINY_LOGOUT,NULL,apiUsrMgnt_DELETE_Logout,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(TINY_USER_ALIVE,NULL,apiUsrMgnt_userid_keepalive,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(TINY_REGIONMGNT,NULL,region_setup,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
            if(SQLITE_OK != db_insertInterfae(TINY_REGION_QUERYALL,NULL,region_query_all,NULL,NULL))
                return MYSQLITE3_INSERT_FAIL;
			xeLOG_DEBUG("insert umap success");
            return MYSQLITE3_OK;
      } 
}
int  db_httpHandleCbk(void *data, int argc, char **argv, char **azColName){
   int i;
   UrlMap test;
   long m_get,m_post,m_delete,m_query;
   memset(&test,0,sizeof(test));
   HISOME_urlCorrect=true;

	// // STD_print("argc:%d",argc);
	// for(i=0; i<argc; i++)
	// 	STD_print("%s = %s\n", azColName[i], argv[i]);
	
    // sleep(2);

   for(i=0; i<argc; i++){
      if(!strcmp(azColName[i],"h_url")){
          strcpy(test.h_url,argv[i] ? argv[i] : "NULL");
      }else if(!strcmp(azColName[i],"m_get")){
         sscanf(argv[i],"%ld",&m_get);///Get the get_func's pointer
          test.m_get = (void *)m_get;
      }else if(!strcmp(azColName[i],"m_post")){
         sscanf(argv[i],"%ld",&m_post);///Get the post_func's pointer
          test.m_post = (void *)m_post;
      }else if(!strcmp(azColName[i],"m_delete")){
         sscanf(argv[i],"%ld",&m_delete);///Get the delete_func's pointer
          test.m_delete = (void *)m_delete;
      }else if(!strcmp(azColName[i],"m_query")){
         sscanf(argv[i],"%ld",&m_query);///Get the query_func's pointer
          test.m_query = (void *)m_query;
      }
   }
   if(test.m_get){
       http_handle testHello=test.m_get;
       testHello(data);
   }
   if(test.m_post){
       http_handle testHello=test.m_post;
       testHello(data);
   }
   if(test.m_delete){
       http_handle testHello=test.m_delete;
       testHello(data);
   }
   if(test.m_query){
       http_handle testHello=test.m_query;
       testHello(data);
   }
   return 0;
}
void db_selectUrlMap(MyHttpPack myhttppack){
    char *errMsg;
    int rc=MYSQLITE3_OK;
    char sqlcmd[HISOME_LEN_1024]={'\0'};
    switch(myhttppack.urlmap.method){
          case HTTP_METHOD_GET:sprintf(sqlcmd,"select m_get from %s where h_url=\"%s\"",URLMAP_TBL,myhttppack.urlmap.url);break;
          case HTTP_METHOD_POST:sprintf(sqlcmd,"select m_post from %s where h_url=\"%s\"",URLMAP_TBL,myhttppack.urlmap.url);break;
          case HTTP_METHOD_DELETE:sprintf(sqlcmd,"select m_delete from %s where h_url=\"%s\"",URLMAP_TBL,myhttppack.urlmap.url);break;
          case HTTP_METHOD_QUERY:sprintf(sqlcmd,"select m_query from %s where h_url=\"%s\"",URLMAP_TBL,myhttppack.urlmap.url);break;
          default :sprintf(sqlcmd,"select m_get from %s where h_url=\"%s\"",URLMAP_TBL,myhttppack.urlmap.url);break;
    }
    // HISOME_urlCorrect= false while  get a wrong url request and return http 400
    HISOME_urlCorrect=false;

    rc = sqlite3_exec(g_urlMapDB,sqlcmd, db_httpHandleCbk, (void *)(&myhttppack), &errMsg);

    if( rc != SQLITE_OK ){
          PRTLOG_ERR("SQL error: %s\n", errMsg);
          sqlite3_free(errMsg);
    }else{

        if(HISOME_urlCorrect == false){
            mg_printf(myhttppack.httpReq.nc,"%s","HTTP/1.1 400\r\nContent-Length: 0\r\n\r\n");
        }
    }

}



int  db_createGeneralTbl(sqlite3 *db,const char *cmd,char *tblName)
{
    char sqlCmd[HISOME_LEN_1024]={'\0'};
    sprintf(sqlCmd,cmd,tblName);
    if(SQLITE_OK  != db_generalExec(db, sqlCmd)){
        PRTLOG_ERR("create table:%s fail",tblName);
        return MYSQLITE3_CREATE_TABLE_FAIL;
    }
    return MYSQLITE3_OK;
}


int  db_generalExec(sqlite3 *db,char * cmd){
    if(0==com_pthread_mutex_lock(&transaction_syn_mutx,"transaction_syn_mutx.mutex","db_generalExec",LINE)){
        int rc=0;
        char *errMsg;
        // g_dbChangedTimes
        rc = sqlite3_exec(db,cmd, NULL, 0, &errMsg);
        if(SQLITE_OK != rc){ 
            // PRTLOG_INFO("do:%s fail",cmd);
            PRTLOG_ERR("Sqlite3 errMsg:%s",errMsg);
            sqlite3_free(errMsg);
            com_pthread_mutex_unlock(&transaction_syn_mutx,"transaction_syn_mutx.mutex","db_generalExec",LINE);
            return MYSQLITE3_UPDATE_FAIL;
        }
        com_pthread_mutex_unlock(&transaction_syn_mutx,"transaction_syn_mutx.mutex","db_generalExec",LINE);
    }
	// STD_print();
    return MYSQLITE3_OK;
}

void  sysMgnt_exit()
{
	db_closeDb(g_urlMapDB);
    sleep(1);
    exit(0);
}
int  db_insertInterfae(const char * h_url,http_handle m_get,http_handle m_post,http_handle m_delete,http_handle m_query){
    UrlMap urlmap;
    memset(&urlmap,0,sizeof(urlmap));
    strcpy(urlmap.h_url,h_url);
    urlmap.m_get = (m_get==NULL)?NULL:m_get;
    urlmap.m_post = (m_post==NULL)?NULL:m_post;
    urlmap.m_delete = (m_delete==NULL)?NULL:m_delete;
    urlmap.m_query = (m_query==NULL)?NULL:m_query;
    return db_insertUrlMap(g_urlMapDB,&urlmap);
}

int tiny_version_update()
{
    char sqlCmd[128]="";
    int new_ver=SQLITE_VER;
    snprintf(sqlCmd,128-1,"UPDATE version SET  ver = %d WHERE ID = 1;",new_ver);
    if(SQLITE_OK  != db_generalExec(g_sysMgntDB, sqlCmd)){
        xeLOG_ERR("update table:%s fail",IPCMGNT_VER);
    }
    xeLOG_NOTICE("update version:%d",new_ver);
    return 0;
}

int tiny_get_version()
{
	int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
	int i,j;
    char sqlCmd[128]="";
    int ver_in_sqlite = 0;
    
    snprintf(sqlCmd,128-1,"select * from version WHERE ID = 1;");
	rc = sqlite3_get_table(g_sysMgntDB,sqlCmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s",IPCMGNT_VER,errMsg);
		sqlite3_free(errMsg);
	}else{
		for(i=0;i<rows;i++){
			for(j=0;j<columns;j++){
				if(!strcmp(qResult[j], "ver")){
					ver_in_sqlite= atoi(qResult[columns*(i+1)+j]);	
				}
			}
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}
    xeLOG_NOTICE("ver_in_sqlite :%d",ver_in_sqlite);
    return ver_in_sqlite;
}
int tiny_rec_sqlite_main()
{
    int db_version = 0;

	g_sysMgntDB = tiny_sqlite3_open(SYSMGNT_DB);
	if(!g_sysMgntDB){
		xeLOG_ERR("Can't open database: %s",SYSMGNT_DB);
		return MYSQLITE3_CREATE_TABLE_FAIL;
	}

	if(SQLITE_OK != db_createGeneralTbl(g_sysMgntDB,sql_create_ver_table, IPCMGNT_VER)){
	    xeLOG_ERR("Create table %s in database %s Fail",IPCMGNT_VER,SYSMGNT_DB);
	}else{
        char sqlcmd[128]="";
        sprintf(sqlcmd,"insert into version (ver) values(%d);",SQLITE_VER); 
        if(SQLITE_OK  != db_generalExec(g_sysMgntDB, sqlcmd)){
            xeLOG_ERR("insert table:%s fail",IPCMGNT_VER);
        }
    }

	if(SQLITE_OK != db_createGeneralTbl(g_sysMgntDB,sql_create_ipcmgnt_table, IPCMGNT_IPCMGNT_TBL)){
	    xeLOG_ERR("Create table %s in database %s Fail",IPCMGNT_IPCMGNT_TBL,SYSMGNT_DB);
	}

	if(SQLITE_OK != db_createGeneralTbl(g_sysMgntDB,sql_create_spacemgnt_table, HARD_DISK_SPACE_MGNT_TBL)){
	        xeLOG_ERR("Create table %s in database %s Fail",HARD_DISK_SPACE_MGNT_TBL,SYSMGNT_DB);

	}

    g_userMgntDb= tiny_sqlite3_open(USERMGNT_DB);
    if(!g_userMgntDb){
        xeLOG_ERR("Can't open database: %s",USERMGNT_DB);
        return MYSQLITE3_CREATE_TABLE_FAIL;
    }
    userMgnt_fillDB(g_userMgntDb);
    
    if(MYSQLITE3_OK !=db_openDB()){
        PRTLOG_ERR("db_open fail");
		goto err_init;
    }
    if(MYSQLITE3_OK != db_fillUrlMapDB()){
        PRTLOG_ERR("db_fillUrlMapDB fail");
		goto err_init;
    }
    if(MYSQLITE3_OK != tiny_region_init()){
        PRTLOG_ERR("tiny_region_init fail");
		goto err_init;
    }

    db_version = tiny_get_version();

    if(db_version < 20200729){
        char sqlCmd[128]="ALTER TABLE ipcMgnt ADD COLUMN collector_name char(256);";
        if(SQLITE_OK  != db_generalExec(g_sysMgntDB, sqlCmd)){
            xeLOG_ERR("ALTER table:%s fail",IPCMGNT_VER);
        } 
    }
    if(db_version < 20200805){
        char sqlCmd[128]="ALTER TABLE ipcMgnt ADD COLUMN ipc_name char(256);";
        if(SQLITE_OK  != db_generalExec(g_sysMgntDB, sqlCmd)){
            xeLOG_ERR("ALTER table:%s fail",IPCMGNT_VER);
        } 
    }
    tiny_version_update();

    xeLOG_NOTICE("tiny_rec_sqlite_main init success");
	return MYSQLITE3_OK;
err_init:
	sysMgnt_exit();
	return MYSQLITE3_CREATE_TABLE_FAIL;
}

/**
 * @Date: 2020-05-10 17:49:30
 * @Description: start for sysmgnt
 * @Return: 
 */

int tiny_query_sysmgntdb_ipc(t_ipc_query_info *ipc_info)
{
	int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
	int i,j;
	char sqlcmd[HISOME_LEN_128];
	sprintf(sqlcmd,"select * from %s where ipc_sn like '%s'",IPCMGNT_IPCMGNT_TBL,ipc_info->ipc_sn); 
	rc = sqlite3_get_table(g_sysMgntDB,sqlcmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				IPCMGNT_IPCMGNT_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
		// PRTLOG_INFO("rows %d,columns:%d",rows,columns);
		for(i=0;i<rows;i++){
			for(j=0;j<columns;j++){
#ifdef DEBUG
				if(0 == i){
					// xeLOG_DEBUG("qResult[j]:%s v:%s",qResult[j],qResult[columns*(i+1)+j]);
				}
#endif
				if(!strcmp(qResult[j], "storage_path")){
					strcpy((char *)ipc_info->storage_path,qResult[columns*(i+1)+j]);	
				}else if(!strcmp(qResult[j], "harddisk_dir")){
                    strcpy((char *)ipc_info->harddisk_dir,qResult[columns*(i+1)+j]);
				}else if(!strcmp(qResult[j], "harddisk_sn")){
                    strcpy((char *)ipc_info->harddisk_sn,qResult[columns*(i+1)+j]);
				}else{
                    
                }
			}
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}
    return 0;
}

int tiny_query_sysmgntdb_space(t_space_query_info *space_info)
{
	int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
	int i,j;
	char sqlcmd[HISOME_LEN_1024];

	sprintf(sqlcmd,"select * from %s where harddisk_dir like '%s'",HARD_DISK_SPACE_MGNT_TBL,space_info->harddisk_dir); 
	rc = sqlite3_get_table(g_sysMgntDB,sqlcmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				HARD_DISK_SPACE_MGNT_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
        if(rows != 1){
            PRTLOG_ERR("query,error,sqlcmd:%s",sqlcmd);
            rc = -1;
        }
		for(i=0;i<rows;i++){
			for(j=0;j<columns;j++){
#ifdef DEBUG
				if(0 == i){
					// xeLOG_DEBUG("qResult[j]:%s v:%s",qResult[j],qResult[columns*(i+1)+j]);
				}
#endif
				if(!strcmp(qResult[j], "dir_limit")){
					space_info->dir_limit = atoi(qResult[columns*(i+1)+j]); 
				}else if(!strcmp(qResult[j], "dir_used")){
					space_info->dir_used = atoi(qResult[columns*(i+1)+j]); 
				}else if(!strcmp(qResult[j], "dir_free")){
					space_info->dir_free = atoi(qResult[columns*(i+1)+j]); 
				}else if(!strcmp(qResult[j], "harddisk_sn")){
                    strcpy((char *)space_info->harddisk_sn,qResult[columns*(i+1)+j]);
                }
			}
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}
    return rc;
}

int tiny_rec_query_sysmgntdb_info(e_tbl_type tbl_type,void *data)
{
	int rc = MYSQLITE3_OK;

	if(!g_sysMgntDB){
		g_sysMgntDB = tiny_sqlite3_open(SYSMGNT_DB);
		if(!g_sysMgntDB){
			xeLOG_ERR("Can't open database: %s",SYSMGNT_DB);
			return MYSQLITE3_CREATE_TABLE_FAIL;
		}
	}
    if(tbl_type == E_TBL_IPCMGNT){
        t_ipc_query_info *ipc_info = (t_ipc_query_info *)data;
        rc = tiny_query_sysmgntdb_ipc(ipc_info);
    }else if (tbl_type == E_TBL_SPACE){
        t_space_query_info *space_info = (t_space_query_info *)data;
        rc = tiny_query_sysmgntdb_space(space_info);
    }
	// if(g_sysMgntDB){
	// 	sqlite3_close(g_sysMgntDB);
	// 	g_sysMgntDB = NULL;
	// }
	return rc;
}

int tiny_rec_query_sysmgntdb_ipclist(e_query_type query_type,char *collector_sn,void *data)
{
 	int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
	int i,j;
	char sqlcmd[HISOME_LEN_128];
    vector_t *p_vector = (vector_t *)data;
	if(!g_sysMgntDB){
		g_sysMgntDB = tiny_sqlite3_open(SYSMGNT_DB);
		if(!g_sysMgntDB){
			xeLOG_ERR("Can't open database: %s",SYSMGNT_DB);
			return MYSQLITE3_CREATE_TABLE_FAIL;
		}
	}
    if(query_type == E_QUERY_IPCLIST_ALL){
	    sprintf(sqlcmd,"select * from %s",IPCMGNT_IPCMGNT_TBL);
    }else if(query_type == E_QUERY_IPCLIST_BY_GTSSN){
        sprintf(sqlcmd,"select * from %s where collector_sn like '%s'",IPCMGNT_IPCMGNT_TBL,collector_sn);
    }else{
        return -1;
    }
	rc = sqlite3_get_table(g_sysMgntDB,sqlcmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				IPCMGNT_IPCMGNT_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
		PRTLOG_INFO("rows %d,columns:%d",rows,columns);
		for(i=0;i<rows;i++){
            t_ipc_query_info ipc_info ={0};
			for(j=0;j<columns;j++){
				if(!strcmp(qResult[j], "ipc_sn")){
					strcpy((char *)ipc_info.ipc_sn,qResult[columns*(i+1)+j]);	
				}else if (!strcmp(qResult[j], "start_time")){
                    ipc_info.start_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "end_time")){
                    ipc_info.end_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "ipc_name")){
                    if(qResult[columns*(i+1)+j]){
                        strcpy((char *)ipc_info.ipc_name,qResult[columns*(i+1)+j]);
                    }else{
                        strcpy((char *)ipc_info.ipc_name,ipc_info.ipc_sn);
                    }

				}
			}
            vector_push_back(p_vector, ipc_info);
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}
    return 0;
 
	// if(g_sysMgntDB){
	// 	sqlite3_close(g_sysMgntDB);
	// 	g_sysMgntDB = NULL;
	// }
	return rc;
}


int tiny_rec_update_sysmgntdb_tbl(e_tbl_type tbl_type,e_opt_type opt_type, void *data)
{
    int rc;
	char sqlcmd[HISOME_LEN_1024];

	if(!g_sysMgntDB){
		g_sysMgntDB = tiny_sqlite3_open(SYSMGNT_DB);
		if(!g_sysMgntDB){
			xeLOG_ERR("Can't open database: %s",SYSMGNT_DB);
			return MYSQLITE3_CREATE_TABLE_FAIL;
		}
	}

    if(tbl_type == E_TBL_IPCMGNT){
        t_ipc_query_info *ipc_info = (t_ipc_query_info *)data;
        if(opt_type == E_INSERT_RECORD){
            sprintf(sqlcmd,"insert into %s (collector_sn,ipc_sn,ipc_mac,harddisk_sn,harddisk_dir,storage_path) values('%s','%s','%s','%s','%s','%s');",
                IPCMGNT_IPCMGNT_TBL,
                ipc_info->collector_sn,
                ipc_info->ipc_sn,
                ipc_info->ipc_mac,
                ipc_info->harddisk_sn,
                ipc_info->harddisk_dir,
                ipc_info->storage_path
            );
        }else if (opt_type == E_UPDATE_RECORD){
            sprintf(sqlcmd,"update %s SET start_time = %ld,end_time = %ld WHERE ipc_sn like '%s';",
                IPCMGNT_IPCMGNT_TBL,
                ipc_info->start_time,
                ipc_info->end_time,
                ipc_info->ipc_sn
            );  
        }else if (opt_type == E_DELETE_RECORD){
            sprintf(sqlcmd,"DELETE FROM %s WHERE  ipc_sn like '%s';",
                IPCMGNT_IPCMGNT_TBL,
                ipc_info->ipc_sn
            );  
        }
    }else if (tbl_type == E_TBL_SPACE){
        t_space_query_info *space_info = (t_space_query_info *)data;
        if(opt_type == E_INSERT_RECORD){
            sprintf(sqlcmd,"insert into %s (harddisk_sn,harddisk_dir,dir_limit,dir_free)    \
                values('%s','%s',%d,%d);",
                HARD_DISK_SPACE_MGNT_TBL,
                space_info->harddisk_sn,
                space_info->harddisk_dir,
                space_info->dir_limit,
                space_info->dir_free
            );
        }else if (opt_type == E_UPDATE_RECORD){
            sprintf(sqlcmd,"UPDATE %s SET dir_limit = %d,dir_used = %d, dir_free =%d  \
                WHERE harddisk_dir like '%s';",
                HARD_DISK_SPACE_MGNT_TBL,
                space_info->dir_limit,
                space_info->dir_used,
                space_info->dir_free,
                space_info->harddisk_dir
            );
        }
    }

    // xeLOG_INFO("do:%s",sqlcmd);
    rc =  db_generalExec(g_sysMgntDB,sqlcmd);
    if(SQLITE_OK  != rc){
        STD_print(":%s fail",sqlcmd);
        xeLOG_ERR(":%s fail",sqlcmd);
        return MYSQLITE3_CREATE_TABLE_FAIL;
    }
	// if(g_sysMgntDB){
	// 	sqlite3_close(g_sysMgntDB);
	// 	g_sysMgntDB = NULL;
	// }
    return rc;
}

/**
 * @Date: 2020-05-10 17:50:07
 * @Description: start for ipc.db
 * @Return: 
 */
int tiny_ipc_db_init(const char *path)
{
    int retval = 0;
    char file_name[HISOME_LEN_256];
    sqlite3 *ipc_db  =NULL;
	snprintf(file_name,sizeof(file_name)-1,"%s/%s",path,IPC_DB_FILENAME);

    pthread_mutex_lock(&ipcdb_mutex);
    ipc_db = tiny_sqlite3_open(file_name);
	if(!ipc_db){
		xeLOG_ERR("Can't open database: %s",path);
		retval =  MYSQLITE3_CREATE_TABLE_FAIL;
        goto init_exit;
	}
    xeLOG_DEBUG("create :%s",file_name);
    
    if(SQLITE_OK != db_createGeneralTbl(ipc_db,sql_create_file_table,IPC_FILE_TBL)){
        xeLOG_ERR("Create table %s in database %s Fail",IPC_FILE_TBL,path);
		retval =  MYSQLITE3_CREATE_TABLE_FAIL;
        goto init_exit;
    }
    if(SQLITE_OK != db_createGeneralTbl(ipc_db,sql_create_statistics_table,IPC_STATIS_TBL)){
        xeLOG_ERR("Create table %s in database %s Fail",IPC_STATIS_TBL,path);
		retval =  MYSQLITE3_CREATE_TABLE_FAIL;
        goto init_exit;
    }else{
        char sqlCmd[HISOME_LEN_1024]={'\0'};
        long long size = ((long long)PER_IPC_SPACE_GB<<30);
        sprintf(sqlCmd,"insert into %s (space_limit) values(%lld);",IPC_STATIS_TBL,size);
        if(SQLITE_OK  != db_generalExec(ipc_db, sqlCmd)){
            xeLOG_ERR("create table:%s fail",IPC_STATIS_TBL);
            retval =  MYSQLITE3_CREATE_TABLE_FAIL;
            goto init_exit;
        }
        STD_print("%s",sqlCmd);
    }
    tiny_sqlite3_close(ipc_db);
init_exit:
    pthread_mutex_unlock(&ipcdb_mutex);
    return retval;
}


int  tiny_get_statistics_ipcdb(const char *path,void *data)
{
    int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
    int i,j;
    char sqlCmd[HISOME_LEN_1024]={'\0'};
    sqlite3 *ipc_db  =NULL;
    char file_name[HISOME_LEN_256];
    t_ipc_statis_des *p_statis_des = (t_ipc_statis_des *)data;

    pthread_mutex_lock(&ipcdb_mutex);
	snprintf(file_name,sizeof(file_name)-1,"%s/%s",path,IPC_DB_FILENAME);
    // xeLOG_NOTICE("file_name:%s",file_name);
    ipc_db = tiny_sqlite3_open(file_name);
	if(!ipc_db){
		// xeLOG_ERR("Can't open database: %s",path);
        rc =  MYSQLITE3_CREATE_TABLE_FAIL;
        goto statis_exit;
	}

	sprintf(sqlCmd,"select * from %s",IPC_STATIS_TBL); 
	rc = sqlite3_get_table(ipc_db,sqlCmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				IPC_STATIS_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
        if(rows != 1){
            xeLOG_ERR("query,error");
            rc = -1;
            // goto statis_exit;
        }
		for(i=0;i<rows;i++){
			for(j=0;j<columns;j++){
#ifdef DEBUG
				if(0 == i){
					// xeLOG_DEBUG("qResult[j]:%s v:%s",qResult[j],qResult[columns*(i+1)+j]);
				}
#endif
				if(!strcmp(qResult[j], "total_space")){
                    p_statis_des->total_space = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "space_limit")){
                    p_statis_des->space_limit = atoll(qResult[columns*(i+1)+j]); 
				}else if (!strcmp(qResult[j], "num_files")){
                    p_statis_des->num_files = atoi(qResult[columns*(i+1)+j]); 
				}else if (!strcmp(qResult[j], "start_time")){
                    p_statis_des->start_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "end_time")){
                    p_statis_des->end_time = atoll(qResult[columns*(i+1)+j]);
				}
			}
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}
    tiny_sqlite3_close(ipc_db);
statis_exit:
    pthread_mutex_unlock(&ipcdb_mutex);
    return rc;
}

int get_one_record_from_ipcdb(e_query_type query_type,const char *path,t_ipc_file_tbl_des *file_des)
{
    int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
    int i,j;
    char sqlCmd[HISOME_LEN_1024]={'\0'};
    sqlite3 *ipc_db  =NULL;
    char file_name[HISOME_LEN_256];

	snprintf(file_name,sizeof(file_name)-1,"%s/%s",path,IPC_DB_FILENAME);
    pthread_mutex_lock(&ipcdb_mutex);
    ipc_db = tiny_sqlite3_open(file_name);
	if(!ipc_db){
		xeLOG_ERR("Can't open database: %s",path);
		rc =  MYSQLITE3_CREATE_TABLE_FAIL;
        goto query_record_exit;
	}

    if(query_type == E_QUERY_OLDEST){
	    sprintf(sqlCmd,"select * from %s order by start_time ASC limit 0,1;",IPC_FILE_TBL);
    }else if(query_type == E_QUERY_NEWEST){
        sprintf(sqlCmd,"select * from %s order by end_time DESC limit 0,1;",IPC_FILE_TBL);
    }else if(query_type == E_QUERY_BY_NAME){
        sprintf(sqlCmd,"select * from %s where file_name like '%s';",IPC_FILE_TBL,file_des->file_name);
    }else{
        
    }
	rc = sqlite3_get_table(ipc_db,sqlCmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				IPC_FILE_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
        if(rows < 1){
            xeLOG_ERR("query,error");
            rc =  MYSQLITE3_CREATE_TABLE_FAIL;
        }
		for(i=0;i<rows;i++){
			for(j=0;j<columns;j++){
#ifdef DEBUG
				if(0 == i){
					//xeLOG_DEBUG("qResult[j]:%s v:%s",qResult[j],qResult[columns*(i+1)+j]);
				}
#endif

				if(!strcmp(qResult[j], "file_name")){
                    strcpy((char *)file_des->file_name,qResult[columns*(i+1)+j]);	
				}else if (!strcmp(qResult[j], "detail_info_name")){
                    strcpy((char *)file_des->detail_info_name,qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "start_time")){
                    file_des->start_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "end_time")){
                    file_des->end_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "duration")){
                    file_des->duration = atoi(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "file_size")){
                    file_des->file_size = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "file_space")){
                    file_des->file_space = atoll(qResult[columns*(i+1)+j]);
				}
			}
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}

    tiny_sqlite3_close(ipc_db);
query_record_exit:
    pthread_mutex_unlock(&ipcdb_mutex);
    return rc;
}

char *sql_select_ipcdb_durInfo_all  =   
										"SELECT * FROM %s WHERE "\
										"start_time < end_time "\
										"AND (NOT(start_time >= %ld OR end_time <= %ld)) order by start_time;";

int get_reccords_from_ipctable(e_query_type query_type,
    t_ipc_query_info *ipc_info,t_ipc_query_result *query_result)
{
    int rc = MYSQLITE3_OK;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
    int i,j;
    char sqlCmd[HISOME_LEN_1024]={'\0'};
    sqlite3 *ipc_db  =NULL;
    char file_name[HISOME_LEN_256];
    t_file_opt_info temp_opt_des={0};
    t_ipc_file_tbl_des *p_file_des = &temp_opt_des.file_des;

	query_result->start_time = ipc_info->start_time;
	query_result->end_time   = ipc_info->end_time;
	
	snprintf(file_name,sizeof(file_name)-1,"%s/%s",ipc_info->storage_path,IPC_DB_FILENAME);
    pthread_mutex_lock(&ipcdb_mutex);
    ipc_db = tiny_sqlite3_open(file_name);
	if(!ipc_db){
		xeLOG_ERR("Can't open database: %s",file_name);
        pthread_mutex_unlock(&ipcdb_mutex);
		return MYSQLITE3_CREATE_TABLE_FAIL;
	}

    if(query_type == E_QUERY_BY_TIME){
        sprintf(sqlCmd,sql_select_ipcdb_durInfo_all,
            IPC_FILE_TBL,
            ipc_info->end_time,
            ipc_info->start_time);
    }
    xeLOG_DEBUG("%s:%s",file_name,sqlCmd);
	rc = sqlite3_get_table(ipc_db,sqlCmd,&qResult,&rows,&columns,&errMsg);
	if(rc != SQLITE_OK){
		xeLOG_ERR("select table:%s fail:SQL error: %s\n",
				IPC_FILE_TBL,errMsg);
		sqlite3_free(errMsg);
	}else{
        if(rows < 1){
            xeLOG_NOTICE("no records foundr");
            rc = -1;
        }
		for(i=0;i<rows;i++){
            memset(&temp_opt_des, 0 , sizeof(t_file_opt_info));
			for(j=0;j<columns;j++){
#if 0
				if(0 == i){
					xeLOG_DEBUG("qResult[j]:%s v:%s",qResult[j],qResult[columns*(i+1)+j]);
				}
#endif
				if(!strcmp(qResult[j], "file_name")){
                    strcpy((char *)p_file_des->file_name,qResult[columns*(i+1)+j]);	
				}else if (!strcmp(qResult[j], "detail_info_name")){
                    strcpy((char *)p_file_des->detail_info_name,qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "start_time")){
                    p_file_des->start_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "end_time")){
                    p_file_des->end_time = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "duration")){
                    p_file_des->duration = atoi(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "file_size")){
                    p_file_des->file_size = atoll(qResult[columns*(i+1)+j]);
				}else if (!strcmp(qResult[j], "file_space")){
                    p_file_des->file_space = atoll(qResult[columns*(i+1)+j]);
				}
			}
            if(p_file_des->start_time < query_result->start_time){
                temp_opt_des.start_offset_sec = query_result->start_time - p_file_des->start_time;
            }else{
                temp_opt_des.start_offset_sec = 0;
            }
            if(p_file_des->end_time > query_result->end_time){
                temp_opt_des.end_offset_sec = query_result->end_time - p_file_des->start_time;
            }else{
                 temp_opt_des.end_offset_sec =  p_file_des->duration;
            }
            query_result->durNum++;
            vector_push_back(query_result->p_vector, temp_opt_des);
		}
		if(qResult)
			sqlite3_free_table(qResult);
	}

    tiny_sqlite3_close(ipc_db);
    pthread_mutex_unlock(&ipcdb_mutex);
    return rc;
}



int tiny_update_ipcdb(e_opt_type opt_type,const char *path,void *data)
{
    int rc = MYSQLITE3_OK;
    char sqlCmd[HISOME_LEN_1024]={'\0'};
    sqlite3 *ipc_db  =NULL;
    char file_name[HISOME_LEN_256];

	snprintf(file_name,sizeof(file_name)-1,"%s/%s",path,IPC_DB_FILENAME);
    pthread_mutex_lock(&ipcdb_mutex);
    ipc_db = tiny_sqlite3_open(file_name);
	if(!ipc_db){
		xeLOG_ERR("Can't open database: %s",path);
        pthread_mutex_unlock(&ipcdb_mutex);
		return MYSQLITE3_CREATE_TABLE_FAIL;
	}

    if(opt_type == E_INSERT_RECORD){
        t_ipc_file_tbl_des *p_file_des = (t_ipc_file_tbl_des *)data;
        sprintf(sqlCmd,"insert into %s (file_name,detail_info_name,start_time,end_time,duration,file_size,file_space) values('%s', '%s',%ld, %ld, %d, %lld, %lld);",
            IPC_FILE_TBL,
            p_file_des->file_name,
            p_file_des->detail_info_name,
            p_file_des->start_time,
            p_file_des->end_time,
            p_file_des->duration,   
            p_file_des->file_size,
            p_file_des->file_space
        );
    }else if(opt_type == E_UPDATE_RECORD){
        t_ipc_file_tbl_des *p_file_des = (t_ipc_file_tbl_des *)data;
        sprintf(sqlCmd,"UPDATE %s SET file_size = %lld WHERE file_name like '%s';",
            IPC_FILE_TBL,
            p_file_des->file_size,
            p_file_des->file_name
        );
    }else if(opt_type == E_UPDATE_STATIS){
        t_ipc_statis_des *p_statis_des = (t_ipc_statis_des *)data;
        sprintf(sqlCmd,"UPDATE %s SET  num_files = %d, start_time = %ld,\
            end_time = %ld,total_space = %lld  WHERE ID = 1;",
            IPC_STATIS_TBL,
            p_statis_des->num_files,
            p_statis_des->start_time,
            p_statis_des->end_time,
            p_statis_des->total_space
        );
    }else if(opt_type == E_DELETE_RECORD){
        t_ipc_file_tbl_des *p_file_des = (t_ipc_file_tbl_des *)data;
        sprintf(sqlCmd,"DELETE FROM %s WHERE  file_name like '%s';",
            IPC_FILE_TBL,
            p_file_des->file_name
        );
    }
    rc =  db_generalExec(ipc_db,sqlCmd);
    tiny_sqlite3_close(ipc_db);
    pthread_mutex_unlock(&ipcdb_mutex);
    return rc;
}

int db_FileDbSwitchMemDb(sqlite3 *memDb, char *fileDbPathName, int F2M0_M2F1)
{
    return 0;
}


int tiny_region_init()
{
	g_regionMgntDB = tiny_sqlite3_open(REGION_DB);
	if(!g_regionMgntDB){
		xeLOG_ERR("Can't open database: %s",REGION_DB);
		return MYSQLITE3_CREATE_TABLE_FAIL;
	}
    
	if(SQLITE_OK != db_createGeneralTbl(g_regionMgntDB,sql_create_region_tree_table, REGION_TREE_TBL)){
	    xeLOG_ERR("Create table %s in database %s Fail",REGION_TREE_TBL,REGION_DB);
	}else{

    }
    return MYSQLITE3_OK;
}


// const char *sql_create_region_tree_table = "CREATE TABLE %s" \
//                                         "(ID INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE," \
//                                         " indexcode      CHAR (64)   DEFAULT [0]," \
//                                         " region_name     CHAR (256)   DEFAULT [0]," \
//                                         " parent_indexcode     	 CHAR (64)   DEFAULT [0]," \
//                                         " collector_gts     CHAR (64)   DEFAULT [0])"; 

int region_mgnt_opt(e_opt_type opttype,t_region_res *region_in,t_region_query_result *result)
{
    int rc = 0;
	char **qResult=NULL;
	char *errMsg;
	int rows,columns;
    int i,j;
	char sqlcmd[HISOME_LEN_1024];

    t_region_res region_res_tmp={0};
	if(!g_regionMgntDB){
		g_regionMgntDB = tiny_sqlite3_open(REGION_DB);
		if(!g_regionMgntDB){
			xeLOG_ERR("Can't open database: %s",REGION_DB);
			return MYSQLITE3_CREATE_TABLE_FAIL;
		}
	}

    if(opttype == E_QUERY_RECORD){
        sprintf(sqlcmd,"select * from %s where parent_indexcode like '%s';",
            REGION_TREE_TBL,
            region_in->parent_indexcode
        );
        rc = sqlite3_get_table(g_regionMgntDB,sqlcmd,&qResult,&rows,&columns,&errMsg);
        if(rc != SQLITE_OK){
            xeLOG_ERR("select table:%s fail:SQL error: %s\n",
                    REGION_TREE_TBL,errMsg);
            sqlite3_free(errMsg);
        }else{
            if(rows < 1){
                xeLOG_NOTICE("no records foundr");
                rc = 0;
            }
            for(i=0;i<rows;i++){
                memset(&region_res_tmp, 0 , sizeof(t_region_res));
                for(j=0;j<columns;j++){
                    if(!strcmp(qResult[j], "indexcode")){
                        strcpy((char *)region_res_tmp.indexcode,qResult[columns*(i+1)+j]);	
                    }else if (!strcmp(qResult[j], "region_name")){
                        strcpy((char *)region_res_tmp.region_name,qResult[columns*(i+1)+j]);
                    }else if (!strcmp(qResult[j], "collector_gts")){
                        strcpy((char *)region_res_tmp.collector_gts,qResult[columns*(i+1)+j]);
                    }
                }
                result->region_num++;
                vector_push_back(result->p_vector,region_res_tmp);
            }
            if(qResult)
			    sqlite3_free_table(qResult);
        }
        return rc;
    }

    if(opttype == E_INSERT_RECORD){
        if(strcmp(region_in->parent_indexcode,"-1")){
            sprintf(sqlcmd,"select * from %s where indexcode like '%s';",
                REGION_TREE_TBL,
                region_in->parent_indexcode
            );
            rc = sqlite3_get_table(g_regionMgntDB,sqlcmd,&qResult,&rows,&columns,&errMsg);
            if(rc != SQLITE_OK){
                xeLOG_ERR("select table:%s fail:SQL error: %s\n",
                        REGION_TREE_TBL,errMsg);
                sqlite3_free(errMsg);
            }else{
                if(rows < 1){
                    xeLOG_NOTICE("no parent found %s",region_in->parent_indexcode);
                    STD_print("no parent found %s",region_in->parent_indexcode);      
                    rc = -1;
                }
                if(qResult)
                    sqlite3_free_table(qResult);
            }
            if(rc < 0)
                return rc;
        }

        sprintf(sqlcmd,"insert into %s (indexcode,region_name,parent_indexcode,collector_gts) values('%s','%s','%s','%s');",
            REGION_TREE_TBL,
            region_in->indexcode,
            region_in->region_name,
            region_in->parent_indexcode,
            region_in->collector_gts
        );   
    }
    
    if(opttype == E_DELETE_RECORD){
        sprintf(sqlcmd,"DELETE FROM %s WHERE indexcode like '%s';",
            REGION_TREE_TBL,
            region_in->indexcode
        );   
    }else if(opttype == E_UPDATE_RECORD){
        sprintf(sqlcmd,"update %s SET region_name = '%s',collector_gts = '%s' WHERE indexcode like '%s';",
            REGION_TREE_TBL,
            region_in->region_name,
            region_in->collector_gts,
            region_in->indexcode
        );
    }
    rc =  db_generalExec(g_regionMgntDB,sqlcmd);

    return rc;
}

